﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;
using System.Data;
using CC.Common;
using System.Diagnostics;

namespace OpenXmlSdk
{
    public class OpenXMLExcelWriter : IDisposable
    {
        private SpreadsheetDocument _spreadSheet;
        private SharedStringTablePart _shareStringPart;
        private WorksheetPart _CurrentWorksheetPart;
        public IEnumerable<WorksheetPart> WorkSheets
        {
            get { return _spreadSheet.WorkbookPart.WorksheetParts; }
        }

        #region Constrution and dispose


        public OpenXMLExcelWriter(Stream stream)
        {
            _spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            WorkbookPart workbookPart = _spreadSheet.AddWorkbookPart();//根节点
            workbookPart.Workbook = new Workbook();//整个文件只有一个workbook
            
            //创建SharedStringTablePart， 用于管理excel的内容。不管你的数据是在那个excel worksheet里，
            //cell里的值都是存在sharestringtable里的， 然后通过relationshipID进行关联。
            //这里有有一点需要注意， sdk里没有提及。如果要创建多个worksheet，
            //那么一定要设置SharedStringTable 的Count 和UniqueCount 值。 否则创建多个worksheet， 生成的excel 在打开时会报错。
            if (_spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                _shareStringPart = _spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                _shareStringPart = _spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }
            _shareStringPart.SharedStringTable = new SharedStringTable();
            _shareStringPart.SharedStringTable.Count = 1;
            _shareStringPart.SharedStringTable.UniqueCount = 1;

            //_CurrentWorksheetPart = InsertWorksheet(_spreadSheet.WorkbookPart);//创建一个初始Sheet1

        }
        public void Dispose()
        {
            _spreadSheet.Close();
            _spreadSheet.Dispose();
        }
        
        #endregion
        #region Public interface
        /// <summary>
        /// 将对象集合输出到excel
        /// 如果类属性用DataColumnAttribe定义了列属性，列属性会生效
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        public void ListToXlsx<T>(IEnumerable<T>list, int startRow = 1, int startCol = 1)
        {
            if (_CurrentWorksheetPart == null)
            {
                throw new Exception("WorkSheetPart Not Ready. Please add a new worksheet first");
            }
            startCol = startCol < 1 ? 1 : startCol;
            startRow = startRow < 1 ? 1 : startRow;
            WorksheetPart worksheetPart = _CurrentWorksheetPart;
            //写入标题行
            var props = typeof(T).GetProperties().Where(p => !DataColumnAttribute.IsColumnIgnore(p)).ToArray();
            int rownum = startRow;
            for (int i = 0; i < props.Length; i++)
            {
                string value = DataColumnAttribute.GetColumnName(props[i]);
                var valueType = CellValues.SharedString;
                var colNum = i + startCol;
                SetValue(colNum, rownum, value, valueType, _CurrentWorksheetPart);
            }
            rownum++;
            //写入数据
            foreach (var item in list)
            {
                for (int i = 0; i < props.Length; i++)
                {
                    object value = props[i].GetValue(item);
                    var colNum = i + startCol;
                    SetValue(colNum, rownum, value, _CurrentWorksheetPart);

                }
                rownum++;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="startCol">从1开始</param>
        /// <param name="startRow">从1开始</param>
        /// <param name="dt"></param>
        public void DataTableToXlsx(DataTable dt, int startRow = 1, int startCol=1 )
        {
            if (_CurrentWorksheetPart == null)
            {
                throw new Exception("WorkSheetPart Not Ready. Please add a new worksheet first");
            }
            startCol = startCol < 1 ? 1 : startCol;
            startRow = startRow < 1 ? 1 : startRow;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string value = dt.Columns[i].ColumnName;
                var valueType = CellValues.SharedString;
                var colNum = i + startCol;
                var rowNum =  startRow;
                SetValue(colNum, rowNum, value, valueType, _CurrentWorksheetPart);
            }

            int j = 0;
            foreach (DataRow dr in dt.Rows)
            {
                j++;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string value = Convert.IsDBNull(dr[i]) ? "" : dr[i].ToString();
                    var valueType = CellValues.SharedString;
                    var colNum = i + startCol;
                    var rowNum = j +  startRow ;
                    SetValue(colNum, rowNum, value, valueType, _CurrentWorksheetPart);
                }
            }
        }
       
        private void SetValue(int colNum, int rowNum, object value,  WorksheetPart worksheetPart)
        {
            var dataType = GetDataType(value.GetType());
            SetValue(colNum, rowNum, value.ToString(), dataType, worksheetPart);
        }
        /// <summary>
        /// 先把值写入sharestringtable，调用sdk里InsertSharedStringItem方法， 返回值对应的id。
        /// 然后利用sdk 提供的示例方法InsertCellInWorksheet， 
        /// 创建一个新cell最后把值和cell 做关联
        /// 每写一个cell 都要执行以下worksheetPart.Worksheet.Save();
        /// </summary>
        /// <param name="colNum">从1开始</param>
        /// <param name="rowNum">从1开始</param>
        /// <param name="value">要写入的值</param>
        /// <param name="dataType">数据类型，发现只有SharedString正常 2021-8-13</param>
        /// <param name="worksheetPart"></param>
        private void SetValue(int colNum, int rowNum, string value, EnumValue<CellValues> dataType, WorksheetPart worksheetPart)
        {
            string name = GetColumnName(colNum - 1);
            int id = InsertSharedStringItem(value, _shareStringPart);
            Cell cell = InsertCellInWorksheet(name, Convert.ToUInt32(rowNum), worksheetPart);
            
            cell.CellValue = new CellValue(id.ToString());
            cell.DataType = dataType;
            worksheetPart.Worksheet.Save();
#if DEBUG
            Debug.WriteLine($"val={value}, type={dataType}, shareId={id}, cell.DataType={cell.DataType}");
#endif
        }
       
        /// <summary>
        /// 加入一个Sheet. 如果name为空，则
        /// </summary>
        /// <param name="name"></param>
        public void AddNewWorksheet(string name)
        {
            WorkbookPart workbookPart = _spreadSheet.WorkbookPart;
            _CurrentWorksheetPart = InsertWorksheet(workbookPart, name);
           
        }

        #endregion
        #region private static OpenXml methods
        /// <summary>
        /// 添加一个Sheet
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <param name="sheetName">如果sheetName为空，则生成Sheetn的名称</param>
        /// <returns></returns>
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName = "")
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            if (sheets == null) sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            //如果还没有Sheets节点，则创建Sheets节点
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                sheetName = "Sheet" + sheetId;
            }

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);

            workbookPart.Workbook.Save();
            return newWorksheetPart;
        }

        /// <summary>
        /// 写入ShareString,并返回数据ID
        /// </summary>
        /// <param name="text"></param>
        /// <param name="_shareStringPart"></param>
        /// <returns></returns>
        private static int InsertSharedStringItem(string text, SharedStringTablePart _shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (_shareStringPart.SharedStringTable == null)
            {
                _shareStringPart.SharedStringTable = new SharedStringTable();
                _shareStringPart.SharedStringTable.Count = 1;
                _shareStringPart.SharedStringTable.UniqueCount = 1;
            }
            int i = 0;
            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in _shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }
                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            _shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            _shareStringPart.SharedStringTable.Save();

            return i;
        }

        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

       

        // Given a Worksheet and a cell name, verifies that the specified cell exists.
        // If it does not exist, creates a new cell. 
        private static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
        {
            string columnName = GetColumnName(cellName);
            uint rowIndex = GetRowIndex(cellName);

            IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r.RowIndex.Value == rowIndex);

            // If the Worksheet does not contain the specified row, create the specified row.
            // Create the specified cell in that row, and insert the row into the Worksheet.
            if (rows.Count() == 0)
            {
                Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
                Cell cell = new Cell() { CellReference = new StringValue(cellName) };
                row.Append(cell);
                worksheet.Descendants<SheetData>().First().Append(row);
                worksheet.Save();
            }
            else
            {
                Row row = rows.First();

                IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference.Value == cellName);

                // If the row does not contain the specified cell, create the specified cell.
                if (cells.Count() == 0)
                {
                    Cell cell = new Cell() { CellReference = new StringValue(cellName) };
                    row.Append(cell);
                    worksheet.Save();
                }
            }

        }


        #endregion
        #region Utility methods
        // Given a cell name, parses the specified cell to get the column name.
        /// <summary>
        /// 从单元名称中获取列名 AA2 -> AA
        /// </summary>
        /// <param name="cellName"></param>
        /// <returns></returns>
        private static string GetColumnName(string cellName)
        {
            // Create a regular expression to match the column name portion of the cell name.
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellName);

            return match.Value;
        }

        // Given a cell name, parses the specified cell to get the row index.
        /// <summary>
        /// 从单元名称中获取行号 A22 -> 22
        /// </summary>
        /// <param name="cellName"></param>
        /// <returns></returns>
        private static uint GetRowIndex(string cellName)
        {
            // Create a regular expression to match the row index portion the cell name.
            Regex regex = new Regex(@"\d+");
            Match match = regex.Match(cellName);

            return uint.Parse(match.Value);
        }
        private static EnumValue<CellValues> GetDataType(Type type)
        {
            EnumValue<CellValues> dataType;
            switch (type.FullName)
            {
                case "System.DateTime":
                case "System.Nullable`1[System.DateTime]":
                    dataType = CellValues.SharedString;
                    break;

                case "System.Boolean":
                case "System.Nullable`1[System.Boolean]":
                    dataType = null;
                    break;

                case "System.Byte":
                case "System.Nullable`1[System.Byte]":
                case "System.Int16":
                case "System.Nullable`1[System.Int16]":
                case "System.Int32":
                case "System.Nullable`1[System.Int32]":
                case "System.Int64":
                case "System.Nullable`1[System.Int64]":
                case "System.Double":
                case "System.Nullable`1[System.Double]":
                case "System.Single":
                case "System.Nullable`1[System.Single]":
                case "System.Decimal":
                case "System.Nullable`1[System.Decimal]":
                    dataType = null;
                    break;

                case "System.String":
                case "System.DBNull":
                default:
                    dataType = CellValues.SharedString;
                    break;
            }
            //dataType = CellValues.SharedString;//发现只有设置为SharedString外的其他值，excel文件数据不是想要的值，只有SharedString能正常 2021-8-13
            return dataType; 
        }
       /// <summary>
       /// 把列序号转换为列名，如 27 -> AA
       /// </summary>
       /// <param name="index"></param>
       /// <returns></returns>
        private static string GetColumnName(int index)
        {
            string name = "";
            char[] columnNames = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
            int num = index;
            do
            {
                int i = num % 26;
                name = columnNames[i] + name;
                num = num / 26 - 1;
            } while (num > -1);
            if (string.IsNullOrEmpty(name))
                name = "A";
            return name;
        } 
        #endregion
    }

}
